package com.medicine.product.utils;


import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.OSSObject;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.zip.Adler32;
import java.util.zip.CheckedOutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


public class ExcelExportUtil {

    public static ExByteArrayOutputStream toExcel( List<String> columns,List<Map<String,String>> data,String sheetName,String type,String tileName,String invoiceName) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        int rowNum = 0;
        Row row = sheet.createRow(rowNum);
        Cell cell;


        HSSFCellStyle cellStyle = workbook.createCellStyle();        //表格样式
        if(type.equalsIgnoreCase("2")){
            //----------------标题样式---------------------
            HSSFCellStyle titleStyle = workbook.createCellStyle();        //标题样式
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font ztFont = workbook.createFont();
            ztFont.setItalic(false);                     // 设置字体为斜体字
            ztFont.setColor(Font.COLOR_NORMAL);            // 将字体设置为“红色”
            ztFont.setFontHeightInPoints((short)16);    // 将字体大小设置为18px
            ztFont.setFontName("宋体");             // 将“宋体”字体应用到当前单元格上
            ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
//          ztFont.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
//          tFont.setStrikeout(true);                  // 是否添加删除线
            titleStyle.setFont(ztFont);

            cell=row.createCell(0);
            // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
            // 设置单元格内容
            cell.setCellValue(tileName.substring(0,tileName.indexOf(".")));
            cell.setCellStyle(titleStyle);
            rowNum++;


            HSSFCellStyle titleStyle1 = workbook.createCellStyle();        //标题样式
            titleStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            titleStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            Font ztFont1 = workbook.createFont();
            ztFont1.setItalic(false);                     // 设置字体为斜体字
            ztFont1.setColor(Font.COLOR_NORMAL);            // 将字体设置为“红色”
            ztFont1.setFontHeightInPoints((short)11);    // 将字体大小设置为18px
            ztFont1.setFontName("宋体");             // 将“宋体”字体应用到当前单元格上
            ztFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
//          ztFont.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
//          tFont.setStrikeout(true);                  // 是否添加删除线
            titleStyle1.setFont(ztFont1);

            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
            cell.setCellValue("开票名称："+invoiceName);
            cell.setCellStyle(titleStyle1);
            rowNum++;

            row = sheet.createRow(rowNum);
            cell = row.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
            cell.setCellValue(sheetName.substring(0,sheetName.indexOf(".")));
            cell.setCellStyle(titleStyle1);
            rowNum++;

            row = sheet.createRow(rowNum);

            //----------------单元格样式----------------------------------
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            Font cellFont = workbook.createFont();
            cellFont.setItalic(false);                     // 设置字体为斜体字
            cellFont.setColor(Font.COLOR_NORMAL);            // 将字体设置为“红色”
            cellFont.setFontHeightInPoints((short)10);    // 将字体大小设置为18px
            cellFont.setFontName("宋体");             // 字体应用到当前单元格上
//          cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(cellFont);
            cellStyle.setWrapText(true);//设置自动换行
        }

        for (int i=0; i < columns.size(); i ++) {
            cell = row.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(cellStyle);
        }

        Double sou=0.0;
        Double jie=0.0;
        for (Map<String,String> map : data) {
            rowNum++;
            row = sheet.createRow(rowNum);

            for (int i = 0 ; i < columns.size() ; i++){
                row.createCell(i).setCellValue(map.get(columns.get(i)));

                if(i==5&&columns.get(i).equalsIgnoreCase("收款金额")){
                    sou+=Double.parseDouble(map.get(columns.get(i)));
                }
                if(i==6&&columns.get(i).equalsIgnoreCase("结算金额")){
                    jie+=Double.parseDouble(map.get(columns.get(i)));
                }
            }
        }
        if(type.equalsIgnoreCase("2")){
            rowNum++;
            row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue("汇总金额");


            row.createCell(5).setCellValue(sou);
            row.createCell(6).setCellValue(jie);
            rowNum++;
            row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue("本期应结算金额");
            row.createCell(2).setCellValue(jie);

        }
        ExByteArrayOutputStream baos = new ExByteArrayOutputStream();
        workbook.write(baos);
        baos.close();
        return baos;
    }

    public static HttpHeaders getHttpHeads(String name, Date date) throws UnsupportedEncodingException {
        HttpHeaders headers = new HttpHeaders();
        headers.set("Content-Encoding", "UTF-8");
        String fileName = name + ".xlsx";
        fileName = new String(fileName.getBytes("UTF-8"),"iso8859-1");
        headers.setContentDispositionFormData("attachment", fileName);
        headers.setPragma("public");
//        headers.setCacheControl("No-cache");
        headers.setCacheControl("public");
        headers.setDate("Expires", 0);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return headers;
    }

    public static InputStream streamTran(ExByteArrayOutputStream in) {
        return new ByteArrayInputStream(in.toByteArray());
    }


    public static void copyInputStreamToFile(InputStream inputStream, File file)
            throws IOException {

        try (FileOutputStream outputStream = new FileOutputStream(file)) {

            int read;
            byte[] bytes = new byte[1024];

            while ((read = inputStream.read(bytes)) != -1) {
                outputStream.write(bytes, 0, read);
            }

            // commons-io
            //IOUtils.copy(inputStream, outputStream);

        }

    }

    public String getOssFile(HttpServletRequest request, HttpServletResponse response){

        // endpoint以杭州为例，其它region请按实际情况填写，1改为自己的
        String endpoint = "http://oss-cn-hangzhou.aliyuncs.com";
        // 云账号AccessKey有所有API访问权限，建议遵循阿里云安全最佳实践，创建并使用RAM子账号进行API访问或日常运维，请登录 https://ram.console.aliyun.com 创建
        String accessKeyId = "2改为自己的";
        String accessKeySecret = "3改为自己的";
        String bucketName = "4改为自己的";
        //要下载的文件名（Object Name）字符串，中间用‘,’间隔。文件名从bucket目录开始.5改为自己的
        String key = "focus/item/P8c5z8BKWY1533108107411.png,focus/item/fJ4WmmbM5w1533108018272.png";
        try {
            // 初始化
            OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);;
            //6改为自己的名称
            String fileName = "test.zip";
            // 创建临时文件
            File zipFile = File.createTempFile("test", ".zip");
            FileOutputStream f = new FileOutputStream(zipFile);
            /**
             * 作用是为任何OutputStream产生校验和
             * 第一个参数是制定产生校验和的输出流，第二个参数是指定Checksum的类型 （Adler32（较快）和CRC32两种）
             */
            CheckedOutputStream csum = new CheckedOutputStream(f, new Adler32());
            // 用于将数据压缩成Zip文件格式
            ZipOutputStream zos = new ZipOutputStream(csum);

            String[] keylist = key.split(",");
            for (String ossfile : keylist) {
                // 获取Object，返回结果为OSSObject对象
                OSSObject ossObject = ossClient.getObject(bucketName, ossfile);
                // 读去Object内容  返回
                InputStream inputStream = ossObject.getObjectContent();
                // 对于每一个要被存放到压缩包的文件，都必须调用ZipOutputStream对象的putNextEntry()方法，确保压缩包里面文件不同名

                zos.putNextEntry(new ZipEntry(ossfile.split("/")[2]));
                int bytesRead = 0;
                // 向压缩文件中输出数据
                while((bytesRead=inputStream.read())!=-1){
                    zos.write(bytesRead);
                }
                inputStream.close();
                zos.closeEntry(); // 当前文件写完，定位为写入下一条项目
            }
            zos.close();
            String header = request.getHeader("User-Agent").toUpperCase();
            if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
                fileName = URLEncoder.encode(fileName, "utf-8");
                fileName = fileName.replace("+", "%20");    //IE下载文件名空格变+号问题
            } else {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            }
            response.reset();
            response.setContentType("text/plain");
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Location", fileName);
            response.setHeader("Cache-Control", "max-age=0");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

            FileInputStream fis = new FileInputStream(zipFile);
            BufferedInputStream buff = new BufferedInputStream(fis);
            BufferedOutputStream out=new BufferedOutputStream(response.getOutputStream());
            byte[] car=new byte[1024];
            int l=0;
            while (l < zipFile.length()) {
                int j = buff.read(car, 0, 1024);
                l += j;
                out.write(car, 0, j);
            }
            // 关闭流
            fis.close();
            buff.close();
            out.close();

            ossClient.shutdown();
            // 删除临时文件
            zipFile.delete();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
